{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>提交答卷时间</th>\n",
       "      <th>所用时间</th>\n",
       "      <th>来源</th>\n",
       "      <th>来源详情</th>\n",
       "      <th>来自IP</th>\n",
       "      <th>1、您的姓名：</th>\n",
       "      <th>2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</th>\n",
       "      <th>2、卖点展示可信度</th>\n",
       "      <th>2、价格合理性</th>\n",
       "      <th>...</th>\n",
       "      <th>10、价格合理性</th>\n",
       "      <th>10、新鲜感独特性</th>\n",
       "      <th>11、针对导演主持人人选，您的意见有哪些？</th>\n",
       "      <th>12、此商品是否推荐为A档 ？</th>\n",
       "      <th>13、此商品申请为头部新品，您的意见有哪些？</th>\n",
       "      <th>14、(无条件通过)</th>\n",
       "      <th>14、(有条件通过)</th>\n",
       "      <th>14、(有建议通过)</th>\n",
       "      <th>14、(不通过)</th>\n",
       "      <th>总分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2021/2/23 14:56:25</td>\n",
       "      <td>66秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>aaa</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>无</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>2021/2/23 15:01:58</td>\n",
       "      <td>76秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>bbb</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>111</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>2021/2/23 15:07:31</td>\n",
       "      <td>281秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>ccc</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>123</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>2021/2/24 13:34:24</td>\n",
       "      <td>177秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>ddd</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>无</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2021/2/24 13:44:03</td>\n",
       "      <td>1668秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>eee</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>无</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>2021/2/24 13:47:24</td>\n",
       "      <td>473秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>fff</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>无</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号              提交答卷时间   所用时间  来源  来源详情                    来自IP 1、您的姓名：  \\\n",
       "0   2  2021/2/23 14:56:25    66秒  微信   NaN  101.230.248.194(上海-上海)     aaa   \n",
       "1   3  2021/2/23 15:01:58    76秒  微信   NaN  101.230.248.194(上海-上海)     bbb   \n",
       "2   4  2021/2/23 15:07:31   281秒  微信   NaN  101.230.248.194(上海-上海)     ccc   \n",
       "3   5  2021/2/24 13:34:24   177秒  微信   NaN  101.230.248.194(上海-上海)     ddd   \n",
       "4   6  2021/2/24 13:44:03  1668秒  微信   NaN  101.230.248.194(上海-上海)     eee   \n",
       "5   7  2021/2/24 13:47:24   473秒  微信   NaN  101.230.248.194(上海-上海)     fff   \n",
       "\n",
       "   2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模  2、卖点展示可信度  2、价格合理性  \\\n",
       "0                                                  4               5        5   \n",
       "1                                                  3               3        3   \n",
       "2                                                  2               2        2   \n",
       "3                                                  4               6        5   \n",
       "4                                                  4               5        5   \n",
       "5                                                  5               5        5   \n",
       "\n",
       "   ...  10、价格合理性 10、新鲜感独特性  11、针对导演主持人人选，您的意见有哪些？  12、此商品是否推荐为A档 ？  \\\n",
       "0  ...         3         3                      无                1   \n",
       "1  ...         4         4                    111                2   \n",
       "2  ...         4         4                    123                2   \n",
       "3  ...         5         6                      无                1   \n",
       "4  ...         5         5                      无                1   \n",
       "5  ...         4         4                      无                1   \n",
       "\n",
       "   13、此商品申请为头部新品，您的意见有哪些？  14、(无条件通过)  14、(有条件通过)  14、(有建议通过)  14、(不通过)  总分  \n",
       "0                       2           1           0           0         0  41  \n",
       "1                       1           1           0           0         0  44  \n",
       "2                       1           1           0           0         0  36  \n",
       "3                       1           1           0           0         0  50  \n",
       "4                       1           1           0           0         0  55  \n",
       "5                       1           1           0           0         0  49  \n",
       "\n",
       "[6 rows x 39 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 载入原始数据\n",
    "df_raw = pd.read_excel('./实例文件.xlsx',sheet_name='Sheet1')\n",
    "df_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>序号</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>提交答卷时间</th>\n",
       "      <td>2021/2/23 14:56:25</td>\n",
       "      <td>2021/2/23 15:01:58</td>\n",
       "      <td>2021/2/23 15:07:31</td>\n",
       "      <td>2021/2/24 13:34:24</td>\n",
       "      <td>2021/2/24 13:44:03</td>\n",
       "      <td>2021/2/24 13:47:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>所用时间</th>\n",
       "      <td>66秒</td>\n",
       "      <td>76秒</td>\n",
       "      <td>281秒</td>\n",
       "      <td>177秒</td>\n",
       "      <td>1668秒</td>\n",
       "      <td>473秒</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>来源</th>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>来源详情</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>来自IP</th>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1、您的姓名：</th>\n",
       "      <td>aaa</td>\n",
       "      <td>bbb</td>\n",
       "      <td>ccc</td>\n",
       "      <td>ddd</td>\n",
       "      <td>eee</td>\n",
       "      <td>fff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2、卖点展示可信度</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2、价格合理性</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2、新鲜感独特性</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3、针对导演主持人，您的意见有哪些？\\n</th>\n",
       "      <td>无</td>\n",
       "      <td>111</td>\n",
       "      <td>123</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4、此商品是否推荐为A档？</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5、(无条件通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5、(有条件通过)</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5、(有建议通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5、(不通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6、卖点展示可信度</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6、价格合理性</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6、新鲜感独特性</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7、针对导演主持人人选，您的意见有哪些？</th>\n",
       "      <td>无</td>\n",
       "      <td>111</td>\n",
       "      <td>123</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8、此商品是否推荐为A档？</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9、(无条件通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9、(有条件通过)</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9、(有建议通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9、(不通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10、卖点展示可信度</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10、价格合理性</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10、新鲜感独特性</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11、针对导演主持人人选，您的意见有哪些？</th>\n",
       "      <td>无</td>\n",
       "      <td>111</td>\n",
       "      <td>123</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12、此商品是否推荐为A档 ？</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13、此商品申请为头部新品，您的意见有哪些？</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14、(无条件通过)</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14、(有条件通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14、(有建议通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14、(不通过)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>总分</th>\n",
       "      <td>41</td>\n",
       "      <td>44</td>\n",
       "      <td>36</td>\n",
       "      <td>50</td>\n",
       "      <td>55</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                         0  \\\n",
       "序号                                                                       2   \n",
       "提交答卷时间                                                  2021/2/23 14:56:25   \n",
       "所用时间                                                                   66秒   \n",
       "来源                                                                      微信   \n",
       "来源详情                                                                   NaN   \n",
       "来自IP                                                101.230.248.194(上海-上海)   \n",
       "1、您的姓名：                                                                aaa   \n",
       "2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主...                       4   \n",
       "2、卖点展示可信度                                                                5   \n",
       "2、价格合理性                                                                  5   \n",
       "2、新鲜感独特性                                                                 3   \n",
       "3、针对导演主持人，您的意见有哪些？\\n                                                     无   \n",
       "4、此商品是否推荐为A档？                                                            2   \n",
       "5、(无条件通过)                                                                0   \n",
       "5、(有条件通过)                                                                1   \n",
       "5、(有建议通过)                                                                0   \n",
       "5、(不通过)                                                                  0   \n",
       "6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—...                       3   \n",
       "6、卖点展示可信度                                                                3   \n",
       "6、价格合理性                                                                  3   \n",
       "6、新鲜感独特性                                                                 3   \n",
       "7、针对导演主持人人选，您的意见有哪些？                                                     无   \n",
       "8、此商品是否推荐为A档？                                                            2   \n",
       "9、(无条件通过)                                                                0   \n",
       "9、(有条件通过)                                                                1   \n",
       "9、(有建议通过)                                                                0   \n",
       "9、(不通过)                                                                  0   \n",
       "10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模                        3   \n",
       "10、卖点展示可信度                                                               3   \n",
       "10、价格合理性                                                                 3   \n",
       "10、新鲜感独特性                                                                3   \n",
       "11、针对导演主持人人选，您的意见有哪些？                                                    无   \n",
       "12、此商品是否推荐为A档 ？                                                          1   \n",
       "13、此商品申请为头部新品，您的意见有哪些？                                                   2   \n",
       "14、(无条件通过)                                                               1   \n",
       "14、(有条件通过)                                                               0   \n",
       "14、(有建议通过)                                                               0   \n",
       "14、(不通过)                                                                 0   \n",
       "总分                                                                      41   \n",
       "\n",
       "                                                                         1  \\\n",
       "序号                                                                       3   \n",
       "提交答卷时间                                                  2021/2/23 15:01:58   \n",
       "所用时间                                                                   76秒   \n",
       "来源                                                                      微信   \n",
       "来源详情                                                                   NaN   \n",
       "来自IP                                                101.230.248.194(上海-上海)   \n",
       "1、您的姓名：                                                                bbb   \n",
       "2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主...                       3   \n",
       "2、卖点展示可信度                                                                3   \n",
       "2、价格合理性                                                                  3   \n",
       "2、新鲜感独特性                                                                 3   \n",
       "3、针对导演主持人，您的意见有哪些？\\n                                                   111   \n",
       "4、此商品是否推荐为A档？                                                            2   \n",
       "5、(无条件通过)                                                                0   \n",
       "5、(有条件通过)                                                                1   \n",
       "5、(有建议通过)                                                                0   \n",
       "5、(不通过)                                                                  0   \n",
       "6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—...                       4   \n",
       "6、卖点展示可信度                                                                4   \n",
       "6、价格合理性                                                                  4   \n",
       "6、新鲜感独特性                                                                 4   \n",
       "7、针对导演主持人人选，您的意见有哪些？                                                   111   \n",
       "8、此商品是否推荐为A档？                                                            1   \n",
       "9、(无条件通过)                                                                1   \n",
       "9、(有条件通过)                                                                0   \n",
       "9、(有建议通过)                                                                0   \n",
       "9、(不通过)                                                                  0   \n",
       "10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模                        4   \n",
       "10、卖点展示可信度                                                               4   \n",
       "10、价格合理性                                                                 4   \n",
       "10、新鲜感独特性                                                                4   \n",
       "11、针对导演主持人人选，您的意见有哪些？                                                  111   \n",
       "12、此商品是否推荐为A档 ？                                                          2   \n",
       "13、此商品申请为头部新品，您的意见有哪些？                                                   1   \n",
       "14、(无条件通过)                                                               1   \n",
       "14、(有条件通过)                                                               0   \n",
       "14、(有建议通过)                                                               0   \n",
       "14、(不通过)                                                                 0   \n",
       "总分                                                                      44   \n",
       "\n",
       "                                                                         2  \\\n",
       "序号                                                                       4   \n",
       "提交答卷时间                                                  2021/2/23 15:07:31   \n",
       "所用时间                                                                  281秒   \n",
       "来源                                                                      微信   \n",
       "来源详情                                                                   NaN   \n",
       "来自IP                                                101.230.248.194(上海-上海)   \n",
       "1、您的姓名：                                                                ccc   \n",
       "2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主...                       2   \n",
       "2、卖点展示可信度                                                                2   \n",
       "2、价格合理性                                                                  2   \n",
       "2、新鲜感独特性                                                                 2   \n",
       "3、针对导演主持人，您的意见有哪些？\\n                                                   123   \n",
       "4、此商品是否推荐为A档？                                                            1   \n",
       "5、(无条件通过)                                                                1   \n",
       "5、(有条件通过)                                                                0   \n",
       "5、(有建议通过)                                                                0   \n",
       "5、(不通过)                                                                  0   \n",
       "6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—...                       3   \n",
       "6、卖点展示可信度                                                                3   \n",
       "6、价格合理性                                                                  3   \n",
       "6、新鲜感独特性                                                                 3   \n",
       "7、针对导演主持人人选，您的意见有哪些？                                                   123   \n",
       "8、此商品是否推荐为A档？                                                            1   \n",
       "9、(无条件通过)                                                                0   \n",
       "9、(有条件通过)                                                                1   \n",
       "9、(有建议通过)                                                                0   \n",
       "9、(不通过)                                                                  0   \n",
       "10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模                        4   \n",
       "10、卖点展示可信度                                                               4   \n",
       "10、价格合理性                                                                 4   \n",
       "10、新鲜感独特性                                                                4   \n",
       "11、针对导演主持人人选，您的意见有哪些？                                                  123   \n",
       "12、此商品是否推荐为A档 ？                                                          2   \n",
       "13、此商品申请为头部新品，您的意见有哪些？                                                   1   \n",
       "14、(无条件通过)                                                               1   \n",
       "14、(有条件通过)                                                               0   \n",
       "14、(有建议通过)                                                               0   \n",
       "14、(不通过)                                                                 0   \n",
       "总分                                                                      36   \n",
       "\n",
       "                                                                         3  \\\n",
       "序号                                                                       5   \n",
       "提交答卷时间                                                  2021/2/24 13:34:24   \n",
       "所用时间                                                                  177秒   \n",
       "来源                                                                      微信   \n",
       "来源详情                                                                   NaN   \n",
       "来自IP                                                101.230.248.194(上海-上海)   \n",
       "1、您的姓名：                                                                ddd   \n",
       "2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主...                       4   \n",
       "2、卖点展示可信度                                                                6   \n",
       "2、价格合理性                                                                  5   \n",
       "2、新鲜感独特性                                                                 3   \n",
       "3、针对导演主持人，您的意见有哪些？\\n                                                     无   \n",
       "4、此商品是否推荐为A档？                                                            2   \n",
       "5、(无条件通过)                                                                1   \n",
       "5、(有条件通过)                                                                0   \n",
       "5、(有建议通过)                                                                0   \n",
       "5、(不通过)                                                                  0   \n",
       "6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—...                       3   \n",
       "6、卖点展示可信度                                                                3   \n",
       "6、价格合理性                                                                  2   \n",
       "6、新鲜感独特性                                                                 2   \n",
       "7、针对导演主持人人选，您的意见有哪些？                                                     无   \n",
       "8、此商品是否推荐为A档？                                                            2   \n",
       "9、(无条件通过)                                                                1   \n",
       "9、(有条件通过)                                                                0   \n",
       "9、(有建议通过)                                                                0   \n",
       "9、(不通过)                                                                  0   \n",
       "10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模                        5   \n",
       "10、卖点展示可信度                                                               6   \n",
       "10、价格合理性                                                                 5   \n",
       "10、新鲜感独特性                                                                6   \n",
       "11、针对导演主持人人选，您的意见有哪些？                                                    无   \n",
       "12、此商品是否推荐为A档 ？                                                          1   \n",
       "13、此商品申请为头部新品，您的意见有哪些？                                                   1   \n",
       "14、(无条件通过)                                                               1   \n",
       "14、(有条件通过)                                                               0   \n",
       "14、(有建议通过)                                                               0   \n",
       "14、(不通过)                                                                 0   \n",
       "总分                                                                      50   \n",
       "\n",
       "                                                                         4  \\\n",
       "序号                                                                       6   \n",
       "提交答卷时间                                                  2021/2/24 13:44:03   \n",
       "所用时间                                                                 1668秒   \n",
       "来源                                                                      微信   \n",
       "来源详情                                                                   NaN   \n",
       "来自IP                                                101.230.248.194(上海-上海)   \n",
       "1、您的姓名：                                                                eee   \n",
       "2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主...                       4   \n",
       "2、卖点展示可信度                                                                5   \n",
       "2、价格合理性                                                                  5   \n",
       "2、新鲜感独特性                                                                 5   \n",
       "3、针对导演主持人，您的意见有哪些？\\n                                                     无   \n",
       "4、此商品是否推荐为A档？                                                            2   \n",
       "5、(无条件通过)                                                                1   \n",
       "5、(有条件通过)                                                                0   \n",
       "5、(有建议通过)                                                                0   \n",
       "5、(不通过)                                                                  0   \n",
       "6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—...                       4   \n",
       "6、卖点展示可信度                                                                4   \n",
       "6、价格合理性                                                                  4   \n",
       "6、新鲜感独特性                                                                 3   \n",
       "7、针对导演主持人人选，您的意见有哪些？                                                     无   \n",
       "8、此商品是否推荐为A档？                                                            2   \n",
       "9、(无条件通过)                                                                1   \n",
       "9、(有条件通过)                                                                0   \n",
       "9、(有建议通过)                                                                0   \n",
       "9、(不通过)                                                                  0   \n",
       "10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模                        6   \n",
       "10、卖点展示可信度                                                               5   \n",
       "10、价格合理性                                                                 5   \n",
       "10、新鲜感独特性                                                                5   \n",
       "11、针对导演主持人人选，您的意见有哪些？                                                    无   \n",
       "12、此商品是否推荐为A档 ？                                                          1   \n",
       "13、此商品申请为头部新品，您的意见有哪些？                                                   1   \n",
       "14、(无条件通过)                                                               1   \n",
       "14、(有条件通过)                                                               0   \n",
       "14、(有建议通过)                                                               0   \n",
       "14、(不通过)                                                                 0   \n",
       "总分                                                                      55   \n",
       "\n",
       "                                                                         5  \n",
       "序号                                                                       7  \n",
       "提交答卷时间                                                  2021/2/24 13:47:24  \n",
       "所用时间                                                                  473秒  \n",
       "来源                                                                      微信  \n",
       "来源详情                                                                   NaN  \n",
       "来自IP                                                101.230.248.194(上海-上海)  \n",
       "1、您的姓名：                                                                fff  \n",
       "2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—主...                       5  \n",
       "2、卖点展示可信度                                                                5  \n",
       "2、价格合理性                                                                  5  \n",
       "2、新鲜感独特性                                                                 5  \n",
       "3、针对导演主持人，您的意见有哪些？\\n                                                     无  \n",
       "4、此商品是否推荐为A档？                                                            2  \n",
       "5、(无条件通过)                                                                1  \n",
       "5、(有条件通过)                                                                0  \n",
       "5、(有建议通过)                                                                0  \n",
       "5、(不通过)                                                                  0  \n",
       "6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—...                       3  \n",
       "6、卖点展示可信度                                                                4  \n",
       "6、价格合理性                                                                  3  \n",
       "6、新鲜感独特性                                                                 3  \n",
       "7、针对导演主持人人选，您的意见有哪些？                                                     无  \n",
       "8、此商品是否推荐为A档？                                                            2  \n",
       "9、(无条件通过)                                                                1  \n",
       "9、(有条件通过)                                                                0  \n",
       "9、(有建议通过)                                                                0  \n",
       "9、(不通过)                                                                  0  \n",
       "10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模                        4  \n",
       "10、卖点展示可信度                                                               4  \n",
       "10、价格合理性                                                                 4  \n",
       "10、新鲜感独特性                                                                4  \n",
       "11、针对导演主持人人选，您的意见有哪些？                                                    无  \n",
       "12、此商品是否推荐为A档 ？                                                          1  \n",
       "13、此商品申请为头部新品，您的意见有哪些？                                                   1  \n",
       "14、(无条件通过)                                                               1  \n",
       "14、(有条件通过)                                                               0  \n",
       "14、(有建议通过)                                                               0  \n",
       "14、(不通过)                                                                 0  \n",
       "总分                                                                      49  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先转置一下\n",
    "df_raw = df_raw.T\n",
    "df_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—...</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2、卖点展示可信度</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2、价格合理性</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2、新鲜感独特性</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3、针对导演主持人，您的意见有哪些？\\n</td>\n",
       "      <td>无</td>\n",
       "      <td>111</td>\n",
       "      <td>123</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4、此商品是否推荐为A档？</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5、(无条件通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5、(有条件通过)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5、(有建议通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5、(不通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）...</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>6、卖点展示可信度</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>6、价格合理性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>6、新鲜感独特性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>7、针对导演主持人人选，您的意见有哪些？</td>\n",
       "      <td>无</td>\n",
       "      <td>111</td>\n",
       "      <td>123</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>8、此商品是否推荐为A档？</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>9、(无条件通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>9、(有条件通过)</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>9、(有建议通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>9、(不通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10、卖点展示可信度</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10、价格合理性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10、新鲜感独特性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>11、针对导演主持人人选，您的意见有哪些？</td>\n",
       "      <td>无</td>\n",
       "      <td>111</td>\n",
       "      <td>123</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "      <td>无</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>12、此商品是否推荐为A档 ？</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>13、此商品申请为头部新品，您的意见有哪些？</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>14、(无条件通过)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>14、(有条件通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>14、(有建议通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>14、(不通过)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>总分</td>\n",
       "      <td>41</td>\n",
       "      <td>44</td>\n",
       "      <td>36</td>\n",
       "      <td>50</td>\n",
       "      <td>55</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                index   0    1    2   3   4  \\\n",
       "0   2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—...   4    3    2   4   4   \n",
       "1                                           2、卖点展示可信度   5    3    2   6   5   \n",
       "2                                             2、价格合理性   5    3    2   5   5   \n",
       "3                                            2、新鲜感独特性   3    3    2   3   5   \n",
       "4                                3、针对导演主持人，您的意见有哪些？\\n   无  111  123   无   无   \n",
       "5                                       4、此商品是否推荐为A档？   2    2    1   2   2   \n",
       "6                                           5、(无条件通过)   0    0    1   1   1   \n",
       "7                                           5、(有条件通过)   1    1    0   0   0   \n",
       "8                                           5、(有建议通过)   0    0    0   0   0   \n",
       "9                                             5、(不通过)   0    0    0   0   0   \n",
       "10  6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）...   3    4    3   3   4   \n",
       "11                                          6、卖点展示可信度   3    4    3   3   4   \n",
       "12                                            6、价格合理性   3    4    3   2   4   \n",
       "13                                           6、新鲜感独特性   3    4    3   2   3   \n",
       "14                               7、针对导演主持人人选，您的意见有哪些？   无  111  123   无   无   \n",
       "15                                      8、此商品是否推荐为A档？   2    1    1   2   2   \n",
       "16                                          9、(无条件通过)   0    1    0   1   1   \n",
       "17                                          9、(有条件通过)   1    0    1   0   0   \n",
       "18                                          9、(有建议通过)   0    0    0   0   0   \n",
       "19                                            9、(不通过)   0    0    0   0   0   \n",
       "20  10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模   3    4    4   5   6   \n",
       "21                                         10、卖点展示可信度   3    4    4   6   5   \n",
       "22                                           10、价格合理性   3    4    4   5   5   \n",
       "23                                          10、新鲜感独特性   3    4    4   6   5   \n",
       "24                              11、针对导演主持人人选，您的意见有哪些？   无  111  123   无   无   \n",
       "25                                    12、此商品是否推荐为A档 ？   1    2    2   1   1   \n",
       "26                             13、此商品申请为头部新品，您的意见有哪些？   2    1    1   1   1   \n",
       "27                                         14、(无条件通过)   1    1    1   1   1   \n",
       "28                                         14、(有条件通过)   0    0    0   0   0   \n",
       "29                                         14、(有建议通过)   0    0    0   0   0   \n",
       "30                                           14、(不通过)   0    0    0   0   0   \n",
       "31                                                 总分  41   44   36  50  55   \n",
       "\n",
       "     5  \n",
       "0    5  \n",
       "1    5  \n",
       "2    5  \n",
       "3    5  \n",
       "4    无  \n",
       "5    2  \n",
       "6    1  \n",
       "7    0  \n",
       "8    0  \n",
       "9    0  \n",
       "10   3  \n",
       "11   4  \n",
       "12   3  \n",
       "13   3  \n",
       "14   无  \n",
       "15   2  \n",
       "16   1  \n",
       "17   0  \n",
       "18   0  \n",
       "19   0  \n",
       "20   4  \n",
       "21   4  \n",
       "22   4  \n",
       "23   4  \n",
       "24   无  \n",
       "25   1  \n",
       "26   1  \n",
       "27   1  \n",
       "28   0  \n",
       "29   0  \n",
       "30   0  \n",
       "31  49  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 跳过表头 前 7 行\n",
    "df_raw = df_raw.iloc[7:].reset_index()\n",
    "df_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>分类</th>\n",
       "      <th>商品号</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—...</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2、卖点展示可信度</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>可信度</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2、价格合理性</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2、新鲜感独特性</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）...</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>6、卖点展示可信度</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>可信度</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>6、价格合理性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>6、新鲜感独特性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10、卖点展示可信度</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>可信度</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10、价格合理性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10、新鲜感独特性</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                index  0  1  2  3  4  5  \\\n",
       "0   2、1号商品评分：足金手镯 售价：xxxx 毛利xx%（分值越高表示商品在该维度表现越好）—...  4  3  2  4  4  5   \n",
       "1                                           2、卖点展示可信度  5  3  2  6  5  5   \n",
       "2                                             2、价格合理性  5  3  2  5  5  5   \n",
       "3                                            2、新鲜感独特性  3  3  2  3  5  5   \n",
       "10  6、2号商品 金镶翡翠吊坠 售价xxxxx元 毛利xx%（分值越高表示商品在该维度表现越好）...  3  4  3  3  4  3   \n",
       "11                                          6、卖点展示可信度  3  4  3  3  4  4   \n",
       "12                                            6、价格合理性  3  4  3  2  4  3   \n",
       "13                                           6、新鲜感独特性  3  4  3  2  3  3   \n",
       "20  10、3号商品蜂蜜售价xxxx元 毛利xx%（分值越高表示商品在该维度表现越好）—主流顾客需求规模  3  4  4  5  6  4   \n",
       "21                                         10、卖点展示可信度  3  4  4  6  5  4   \n",
       "22                                           10、价格合理性  3  4  4  5  5  4   \n",
       "23                                          10、新鲜感独特性  3  4  4  6  5  4   \n",
       "\n",
       "       分类 商品号  \n",
       "0    需求规模   1  \n",
       "1     可信度   1  \n",
       "2   价格合理性   1  \n",
       "3     新鲜感   1  \n",
       "10   需求规模   2  \n",
       "11    可信度   2  \n",
       "12  价格合理性   2  \n",
       "13    新鲜感   2  \n",
       "20   需求规模   3  \n",
       "21    可信度   3  \n",
       "22  价格合理性   3  \n",
       "23    新鲜感   3  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每 10 行作循环，提取前 4 行，添加列，并进行拼接\n",
    "df = pd.DataFrame()\n",
    "totol_rows = len(df_raw)\n",
    "nums = totol_rows // 10\n",
    "rows_format = 10 * nums\n",
    "new_list = [[i,i + 10] for i in range(0,rows_format,10)]\n",
    "for i_j,num in zip(new_list,range(nums)):\n",
    "    i,j = i_j\n",
    "    temp_df = df_raw[i:j].iloc[:4]\n",
    "    temp_df['分类'] = ['需求规模','可信度','价格合理性','新鲜感']\n",
    "    temp_df['商品号'] = list(str(num + 1) * 4)\n",
    "    df = df.append(temp_df)\n",
    "df  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>商品号</th>\n",
       "      <th>分类</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>可信度</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>2</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>5</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>3</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>3</td>\n",
       "      <td>可信度</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>3</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>3</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   商品号     分类 variable  value\n",
       "0    1   需求规模        0    4.0\n",
       "1    1    可信度        0    5.0\n",
       "2    1  价格合理性        0    5.0\n",
       "3    1    新鲜感        0    3.0\n",
       "4    2   需求规模        0    3.0\n",
       "..  ..    ...      ...    ...\n",
       "67   2    新鲜感        5    3.0\n",
       "68   3   需求规模        5    4.0\n",
       "69   3    可信度        5    4.0\n",
       "70   3  价格合理性        5    4.0\n",
       "71   3    新鲜感        5    4.0\n",
       "\n",
       "[72 rows x 4 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 逆透视\n",
    "df = df.drop(columns='index')\n",
    "df_melt = pd.melt(df,id_vars=['商品号','分类'])\n",
    "df_melt['value'] = df_melt['value'].astype(float)\n",
    "df_melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>分类</th>\n",
       "      <th>价格合理性</th>\n",
       "      <th>可信度</th>\n",
       "      <th>新鲜感</th>\n",
       "      <th>需求规模</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>商品号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.17</td>\n",
       "      <td>4.33</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.17</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.17</td>\n",
       "      <td>4.33</td>\n",
       "      <td>4.33</td>\n",
       "      <td>4.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     mean                  \n",
       "    value                  \n",
       "分类  价格合理性   可信度   新鲜感  需求规模\n",
       "商品号                        \n",
       "1    4.17  4.33  3.50  3.67\n",
       "2    3.17  3.50  3.00  3.33\n",
       "3    4.17  4.33  4.33  4.33"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 再透视，保留 2 位小数\n",
    "df_pivot = pd.pivot_table(df_melt,index=['商品号'],columns=['分类'],values=['value'],aggfunc=['mean'],fill_value=0).applymap(lambda x:round(x,2))\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "生成结果文件成功！！\n"
     ]
    }
   ],
   "source": [
    "# 拍平多层索引，输出结果\n",
    "df_pivot.columns = ['_'.join(col) for col in df_pivot.columns.values]\n",
    "df_pivot.to_excel('./result.xlsx',sheet_name='result')\n",
    "print('生成结果文件成功！！')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
